<?php 

function getAllDevelopmentTime($userGroup) {
	global $db;
	$sql = "SELECT SUM(`Development_Time`) AS DevelopmentTime FROM `userCake_Users` WHERE (`Group_ID` = $userGroup)";
	
	$result = $db->sql_query($sql);

	$row = $db->sql_fetchrow();
	$minutes = $row['DevelopmentTime']; 
	return round($minutes / 60, 1);
}

function getAllDebuggingTime($userGroup) {
	global $db;
	$sql = "SELECT SUM(`Debugging_Time`) AS DebuggingTime FROM `debugging_Reports` JOIN `userCake_Users` ON `userCake_Users`.`User_Id` = `debugging_Reports`.`User` WHERE `userCake_Users`.`Group_Id` = $userGroup";
	
	$result = $db->sql_query($sql);

	$row = $db->sql_fetchrow();
	$minutes = $row['DebuggingTime']; 
	return round($minutes / 60, 1);
}

function getRecordsCount($userGroup) {
	global $db;
	$sql = "SELECT COUNT(*) AS Count FROM `debugging_Reports` JOIN `userCake_Users` ON `debugging_Reports`.`User` = `userCake_Users`.`User_ID` WHERE `userCake_Users`.`Group_Id` = $userGroup";

	$result = $db->sql_query($sql);

	$row = $db->sql_fetchrow();
	return $row['Count'];
}

function getToolUsagesForCategory($categoryId) {
	global $db;

	$sql = "SELECT COUNT(*) AS Count FROM `debugging_Tool_Usages`
	JOIN `debugging_Reports` ON `debugging_Reports`.`Id` = `debugging_Tool_Usages`.`Debugging_Report`
	JOIN `debugging_Tools` ON `Debugging_Tool` = `debugging_Tools`.`Id`
	JOIN `debugging_Tool_Categories` ON `debugging_Tools`.`CategoryId` = `debugging_Tool_Categories`.`Id`
	JOIN `userCake_Users` ON `debugging_Reports`.`User` = `userCake_Users`.`User_ID`
		
	WHERE (`userCake_Users`.`Group_Id` = 1) AND (`debugging_Tool_Categories`.`Id` = $categoryId)
	GROUP BY `debugging_Reports`.`Id`";

	$result = $db->sql_query($sql);
	if (!$result) {
		$error = $db->sql_error();
		return;
	}

	$rows = $db->sql_fetchall();
	return count($rows);
}


function findDbItem($items, $columnName, $searchedValue) {
	foreach($items as $i => $item) {
		//TODO: hack

		$comparisionString = ' return ($item->' . $columnName . ' == $searchedValue);';
		$match = eval($comparisionString);
		if ($match) {
			return $item;
		}
	}
	
	return null;
}

class CategoryInfo {
	public $subcategoryName;
	public $categoryName;
	public $singleCategoryForAll;

	function __construct($subcategoryName, $categoryName, $singleCategoryForAll) {
		$this->subcategoryName = $subcategoryName;
		$this->categoryName = $categoryName;
		$this->singleCategoryForAll = $singleCategoryForAll;
	}
}

function getDbSingleCategoryInfo($categoryId, $categoryTable) {
	// TODO: generalize for optional depth of category hierarchy
	
	global $db;

	$sql = "SELECT `$categoryTable`.`Name` AS CategoryName  
		FROM `$categoryTable` 
		WHERE `$categoryTable`.`Id` = $categoryId";
	$db->sql_query($sql);
	
	$infoRow = $db->sql_fetchrow();
	$categoryName = $infoRow['CategoryName'];
	
	// HACK, misuse of CategoryInfo 
	$result = new CategoryInfo($categoryName, "", true);
	return $result;	
}

function getDbDoubleCategoryInfo($subcategoryId, $subcategoryTable, $categoryTable) {
	global $db;
	
	$sql = "SELECT COUNT(*) AS Count FROM `$categoryTable`";
	$db->sql_query($sql);
	
	$categoriesCountRow = $db->sql_fetchrow();
	$categoriesCount = $categoriesCountRow['Count'];
	
	// TODO; error condition check; categories count should be always > 0
	
	$singleCategory = ($categoriesCount == 1);

	$sql = "SELECT `$subcategoryTable`.`Name` AS SubcategoryName, `$categoryTable`.`Name` AS CategoryName 
		FROM `$subcategoryTable` JOIN `$categoryTable` ON `CategoryId` = `$categoryTable`.`Id` 
		WHERE `$subcategoryTable`.`Id` = $subcategoryId";
	$db->sql_query($sql);
	
	$infoRow = $db->sql_fetchrow();
	$subcategoryName = $infoRow['SubcategoryName'];
	$categoryName = $infoRow['CategoryName'];
	
	$result = new CategoryInfo($subcategoryName, $categoryName, $singleCategory);
	return $result;	
}

class UsageOption {
	public $usageId;
	public $description;
	
	function __construct($usageId, $description) {
		$this->usageId = $usageId;
		$this->description = $description;
	}	
}

function getUsageCategories() {
	global $db;
	
	$sql = "SELECT Id, Description FROM `usefulness` ORDER BY Id";

	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$usageOptions = array();
	foreach ($sqlItems as $i => $sqlUsageOption) {
	
		$usageOption = new UsageOption($sqlUsageOption['Id'], $sqlUsageOption['Description']);
		array_push($usageOptions, $usageOption); 
	}
	
	return $usageOptions;
}

class DebuggingComplexity {
	public $complexityId;
	public $description;
	
	function __construct($complexityId, $description) {
		$this->complexityId = $complexityId;
		$this->description = $description;
	}	
}

function getDebuggingComplexities() {
	global $db;
	
	$sql = "SELECT Id, Name FROM `root_Cause_Complexities`";

	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$resultItems = array();
	foreach ($sqlItems as $i => $sqlItem) {
	
		$item = new DebuggingComplexity($sqlItem['Id'], $sqlItem['Name']);
		array_push($resultItems, $item); 
	}
	
	return $resultItems;
}

class Feelings {
	public $feelingId;
	public $description;
	
	function __construct($feelingId, $description) {
		$this->feelingId = $feelingId;
		$this->description = $description;
	}	
}

function getFeelings() {
	global $db;
	
	$sql = "SELECT Id, Name FROM `feelings`";

	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$resultItems = array();
	foreach ($sqlItems as $i => $sqlItem) {
	
		$item = new Feelings($sqlItem['Id'], $sqlItem['Name']);
		array_push($resultItems, $item); 
	}
	
	return $resultItems;
}

// TODO: move to some general source file
function startsWith($haystack, $needle)
{
    $length = strlen($needle);
    return (substr($haystack, 0, $length) === $needle);
}

// hmm, I am beginning to have here a real mess with class design
class ReportFlagForStats {
	public $column;
	public $description;
	public $value;
	
	function __construct($flagColumn, $flagDescription, $value) {
		$this->column = $flagColumn;
		$this->description = $flagDescription;
		$this->value = $value;
	}
}

function getReportFlagDescriptions($table) {
	global $db; 	
			
	$sql = "SELECT column_name AS Name,
					column_comment AS Description
			FROM information_schema.columns
			WHERE table_name = '$table'
			AND column_name LIKE 'Flag%'";

	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	return $sqlItems;
}

function getReportFlags($reportId, $table) {
	$reportId = mysql_real_escape_string($reportId);
	$table = mysql_real_escape_string($table);

	$columnsDescriptions = getReportFlagDescriptions($table);
	
	global $db;
	$sql = "SELECT *
			FROM `$table` WHERE `Report_Id` = $reportId";

	$result['error'] = array();
	$result['flags'] = array();
	
	$sqlResult = $db->sql_query($sql);
	if (!$sqlResult) {
		$result['error'][] = $db->sql_error();
		return $result;
	}
	
	$sqlItem = $db->sql_fetchrow();
	foreach ($sqlItem as $columnName => $value) {
		if (!startsWith($columnName, "Flag")) {
			continue;
		}
		
		$description = "";
		foreach ($columnsDescriptions as $j => $columnDescription) {
			if ($columnDescription['Name'] == $columnName) {
				$description = $columnDescription['Description'];
				break;
			}
		}
		
		if ($description == "") {
			$result['error'][] = "Unable to find description for flag $columnName";
			continue;
		}
		
		$flag = new ReportFlagForStats($columnName, $description, $value);
		$result['flags'][] = $flag;
	}
	
	return $result;
}

class Detection {
	public $detectionId;
	public $name;
	public $subcategoryId;
	public $reportId;
	
	function __construct($detectionId, $name, $subcategoryId) {
		$this->detectionId = $detectionId;
		$this->name = $name;
		$this->subcategoryId = $subcategoryId;
	}
}

function getDetections() {
	global $db;
	
	$sql = "SELECT Id, Name, SubcategoryId FROM `detection_Ways`";
	
	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$resultItems = array();
	foreach ($sqlItems as $i => $sqlItem) {
	
		$item = new Detection($sqlItem['Id'], $sqlItem['Name'], $sqlItem['SubcategoryId']);
		array_push($resultItems, $item); 
	}
	
	return $resultItems;
}

class RootCause {
	public $rootCauseId;
	public $name;
	public $subcategoryId;
	
	function __construct($rootCauseId, $name, $subcategoryId) {
		$this->rootCauseId = $rootCauseId;
		$this->name = $name;
		$this->subcategoryId = $subcategoryId;
	}	
}

function getRootCauses() {
	global $db;
	
	$sql = "SELECT Id, Name, SubcategoryId FROM `root_Causes`";

	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$rootCauses = array();
	foreach ($sqlItems as $i => $sqlRootCause) {
	
		$rootCause = new RootCause($sqlRootCause['Id'], $sqlRootCause['Name'], $sqlRootCause['SubcategoryId']);
		array_push($rootCauses, $rootCause); 
	}
	
	return $rootCauses;
}

class ReportUsageItem {
	public $usageId;
	public $dbTable;
	public $reportId;
	public $describedEntity;
	public $usefulness;
	public $categoryId;
	
	function __construct($usageId, $reportId, $dbTable, $describedEntity, $usefulness, $categoryId) {
		$this->usageId = $usageId;
		$this->reportId = $reportId;
		$this->dbTable = $dbTable;
		$this->describedEntity = $describedEntity;
		$this->usefulness = $usefulness;
		$this->categoryId = $categoryId;
	}
}

function getReportUsages($reportId, $dbUsageTable, $dbItemDescriptionTable, $foreingKeyColumn) {
	global $db;
	
	$sql = "SELECT `$dbUsageTable`.`Id` AS UsageId, `$dbItemDescriptionTable`.`Name` AS EntityName, `$dbItemDescriptionTable`.`CategoryId` AS CategoryId, `usefulness`.`Description` AS Usefulness 
			FROM `$dbUsageTable` JOIN `$dbItemDescriptionTable` ON `$foreingKeyColumn` = `$dbItemDescriptionTable`.`Id` JOIN `usefulness` ON `Useful_Usage` = `usefulness`.`Id`
			WHERE `Debugging_Report` = $reportId";
	
	$db->sql_query($sql);
	$sqlItems = $db->sql_fetchall();
	
	$resultItems = array();
	foreach ($sqlItems as $i => $sqlItem) {
	
		$item = new ReportUsageItem($sqlItem['UsageId'], $reportId, $dbUsageTable, $sqlItem['EntityName'], $sqlItem['Usefulness'], $sqlItem['CategoryId']);
		array_push($resultItems, $item); 
	}
	
	return $resultItems;
}

class ReportStats {
	public $reportId;
	public $userId;
	public $debuggingTime;
	public $reportTime;
	public $reviewReason;
	public $reviewed;
	public $complexity;
	public $feelings;
	public $detectionWay;
	public $rootCause;
	public $methods;
	public $tools;
	public $revisionId;
	public $comment;
	
	function __construct($reportId, $userId, $debuggingTime, $reportTime, $reviewReason, $reviewed, $complexity, $feelings,
		$detectionWay, $rootCause, $revisionId, $comment) {
		
		$this->reportId = $reportId;
		$this->userId = $userId;
		$this->debuggingTime = $debuggingTime;
		$this->reportTime = $reportTime;
		$this->reviewReason = $reviewReason;
		$this->reviewed = $reviewed;
		$this->complexity = $complexity;
		$this->feelings = $feelings;
		$this->detectionWay = $detectionWay;
		$this->rootCause = $rootCause;
		$this->revisionId = $revisionId;
		$this->comment = $comment;
	}
	
	public function isReviewPending() {
		return ($this->reviewReason != "") && ($this->reviewed == 0);
	}
}

function loadReportStats($reportId) {
	global $db;
	
	// TODO: RevisionIdentifier -> Revision_Identifier
	$reportId = mysql_real_escape_string($reportId);
	$sql = "SELECT Id, User, Debugging_Time, Report_Time, Review_Reason, Reviewed, Root_Cause_Complexity, Feelings, Detection_Way, Root_Cause,
			RevisionIdentifier, Comment
			FROM `debugging_Reports` WHERE `Id` = $reportId";

	$result['error'] = "";
	$result['report'] = null;
	
	$sqlResult = $db->sql_query($sql);
	if (!$sqlResult) {
		$result['error'] = $db->sql_error();
		return $result;
	}
	
	$sqlItem = $db->sql_fetchrow();
	$result['report'] = new ReportStats($sqlItem['Id'], $sqlItem['User'], $sqlItem['Debugging_Time'], $sqlItem['Report_Time'], $sqlItem['Review_Reason'],
			$sqlItem['Reviewed'], $sqlItem['Root_Cause_Complexity'], $sqlItem['Feelings'], $sqlItem['Detection_Way'], $sqlItem['Root_Cause'],
			$sqlItem['RevisionIdentifier'], $sqlItem['Comment']); 
	
	return $result;
}

class ReportsStats {
	public $reports = array();

	public $error = null;
	
	public function loadStats($userId) {
		$this->reports = array();
		
		global $db;
		$sql = "SELECT Id, Debugging_Time, Report_Time, Review_Reason, Reviewed, Root_Cause_Complexity, Feelings, Detection_Way, Root_Cause, RevisionIdentifier, Comment
				FROM `debugging_Reports` WHERE `User` = $userId";
		
		$result = $db->sql_query($sql);
		if (!$result) {
			$this->error = $db->sql_error();
			return;
		}

		$sqlItems = $db->sql_fetchall();
		
		foreach ($sqlItems as $i => $sqlItem) {
			$this->reports[] = new ReportStats($sqlItem['Id'], $userId, $sqlItem['Debugging_Time'], $sqlItem['Report_Time'], $sqlItem['Review_Reason'],
				$sqlItem['Reviewed'], $sqlItem['Root_Cause_Complexity'], $sqlItem['Feelings'], $sqlItem['Detection_Way'], $sqlItem['Root_Cause'],
				$sqlItem['RevisionIdentifier'], $sqlItem['Comment']); 
		}
	}
	
}

class UserStats {
	
	public $error = null;
	public $userId;
	public $username;
	public $email;
	public $develTime;
	public $debuggingTime;
	public $reportsCount;
	
	function __construct($userId) {
		$this->userId = $userId;
	}
	
	public function loadStats() {
		global $db;
		
		$sql = "SELECT Username, Email, Development_Time  
			FROM `userCake_Users` WHERE `User_ID` = $this->userId";
		
		$result = $db->sql_query($sql);
		if (!$result) {
			$error = $db->sql_error();
			return;
		}
		
		$row = $db->sql_fetchrow();
		
		$this->username = $row['Username'];
		$this->email = $row['Email'];
		$this->develTime = $row['Development_Time'];

		$sql = "SELECT COUNT(*) AS ReportsCount FROM `debugging_Reports` WHERE User = $this->userId";
		$result = $db->sql_query($sql);
		if (!$result) {
			$error = $db->sql_error();
			return;
		}
		
		$row = $db->sql_fetchrow();
		$this->reportsCount = $row['ReportsCount'];

		$sql = "SELECT SUM(`Debugging_Time`) AS DebuggingTime FROM `debugging_Reports` WHERE User = $this->userId";
		$result = $db->sql_query($sql);
		if (!$result) {
			$error = $db->sql_error();
			return;
		}
		
		$row = $db->sql_fetchrow();
		$this->debuggingTime = $row['DebuggingTime'];
	}

	public function updateDevelopmentTime($time) {
		global $db;
		$sql = "UPDATE `userCake_Users` SET `Development_Time` = '$time'
				WHERE `userCake_Users`.`User_ID` = $this->userId;";

		$result = $db->sql_query($sql);
		if (!$result) {
			$error = $db->sql_error();
			return false;
		}
		
		return true;
	}
}

class UserStatsList {

	public $error = null;
	public $users = array();
	
	public function loadStats() {
		global $db;
		
		$sql = "SELECT User_ID  
			FROM `userCake_Users` JOIN `userCake_Groups` ON `userCake_Groups`.Group_ID=`userCake_Users`.`Group_ID`
			WHERE `userCake_Users`.Group_ID = 1";
		
		$result = $db->sql_query($sql);
		if (!$result) {
			$error = $db->sql_error();
			return;
		}
		
		$rows = $db->sql_fetchall();
		if (count($rows) == 0) {
			return;
		}
		
		foreach ($rows as $i => $row) {
			$userId = $row['User_ID'];
			$user = new UserStats($userId);
			$user->loadStats();
			
			if ($user->error != null) {
				$this->error = $user->error;
				return;
			}
			
			array_push($this->users, $user);
		}
	}
}

?>